package com.kfm.bootdemo.dao;

import com.kfm.bootdemo.model.Student;
import com.kfm.bootdemo.util.LoadDatasource;
import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDao {

    private QueryRunner queryRunner;
    BasicRowProcessor basicRowProcessor;

    public StudentDao() {
        try {
            queryRunner = new QueryRunner(LoadDatasource.getDatasource());
            basicRowProcessor = new BasicRowProcessor(new GenerousBeanProcessor());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public List<Student> selectAll() throws SQLException {
        String sql = "select * from student";
        return queryRunner.query(sql, new BeanListHandler<>(Student.class, basicRowProcessor));
    }

    public int deleteById(Integer id) throws SQLException {
        String sql = "delete from student where id = ?";
        return queryRunner.update(sql, id);
    }

    public int editStudent(int id, String name, String birth, String gender) throws SQLException {
        // update  student set name = "张三" where id = 1;
        String sql = "update student set name =  ?, birth  = ?, gender = ? where id = ?";
        return queryRunner.update(sql, name, birth, gender, id);
    }


    /**
     * 分页查询
     * @param pageNum 页码
     * @param pageSize 每页数量
     * @return 分页后的数据
     */
    public List<Student> selectPage(int pageNum, int pageSize, Student student) throws SQLException {
        //
        // String sql = "select * from student limit ?, ?";
        StringBuilder sql = new StringBuilder("select * from student");
        StringBuilder condition = new StringBuilder();
        List<Object> params = new ArrayList<>();
        if (student.getId() != null) {
            condition.append(" where id = ? ");
            params.add(student.getId());
        }
        if (student.getName() != null && !student.getName().isEmpty()) {
            if (condition.isEmpty()) {
                condition.append(" where name = ? ");
            } else {
                condition.append(" and name = ?");
            }
            params.add(student.getName());
        }
        if (student.getBirth() != null) {
            if (condition.isEmpty()) {
                condition.append(" where birth = ? ");
            } else {
                condition.append(" and birth = ?");
            }
            params.add(student.getBirth());
        }
        if (student.getGender() != null && !student.getGender().isEmpty()) {
            if (condition.isEmpty()) {
                condition.append(" where gender = ? ");
            } else {
                condition.append(" and gender = ?");
            }
            params.add(student.getGender());
        }
        if (!condition.isEmpty()) {
            sql.append(condition);
        }
        System.out.println(sql.toString());
        sql.append(" limit ?,? ");
        params.add((pageNum - 1) * pageSize);
        params.add(pageSize);
        return queryRunner.query(sql.toString(), new BeanListHandler<>(Student.class, basicRowProcessor), params.toArray());
    }

    public long selectTotal(Student student) throws SQLException {
        StringBuilder sql = new StringBuilder("select count(*) from student");
        StringBuilder condition = new StringBuilder();
        List<Object> params = new ArrayList<>();
        if (student.getId() != null) {
            condition.append(" where id = ? ");
            params.add(student.getId());
        }
        if (student.getName() != null && !student.getName().isEmpty()) {
            if (condition.isEmpty()) {
                condition.append(" where name = ? ");
            } else {
                condition.append(" and name = ?");
            }
            params.add(student.getName());
        }
        if (student.getBirth() != null) {
            if (condition.isEmpty()) {
                condition.append(" where birth = ? ");
            } else {
                condition.append(" and birth = ?");
            }
            params.add(student.getBirth());
        }
        if (student.getGender() != null && !student.getGender().isEmpty()) {
            if (condition.isEmpty()) {
                condition.append(" where gender = ? ");
            } else {
                condition.append(" and gender = ?");
            }
            params.add(student.getGender());
        }
        if (!condition.isEmpty()) {
            sql.append(condition);
        }
        System.out.println(sql.toString());
        return queryRunner.query(sql.toString(), new ScalarHandler<>(), params.toArray());
    }

    // 为了避免写n个方法
    // 拼接SQL


    public List<Student> selectByStudent(Student student) throws SQLException {
        StringBuilder sql = new StringBuilder("select * from student");
        StringBuilder condition = new StringBuilder();
        List<Object> params = new ArrayList<>();
        if (student.getId() != null) {
            condition.append(" where id = ? ");
            params.add(student.getId());
        }

        if (student.getName() != null && !student.getName().isEmpty()) {
            if (condition.isEmpty()) {
                condition.append(" where name = ? ");
            } else {
                condition.append(" and name = ?");
            }
            params.add(student.getName());
        }
        if (student.getBirth() != null) {
            if (condition.isEmpty()) {
                condition.append(" where birth = ? ");
            } else {
                condition.append(" and birth = ?");
            }
            params.add(student.getBirth());
        }
        if (student.getGender() != null && !student.getGender().isEmpty()) {
            if (condition.isEmpty()) {
                condition.append(" where gender = ? ");
            } else {
                condition.append(" and gender = ?");
            }
            params.add(student.getGender());
        }
        if (!condition.isEmpty()) {
            sql.append(condition);
        }
        System.out.println(sql.toString());
        return queryRunner.query(sql.toString(), new BeanListHandler<>(Student.class, basicRowProcessor), params.toArray());
    }
}
